package private

import (
	"dc-go/global"
	"dc-go/model"
	"encoding/json"
	"errors"
	"fmt"
	"regexp"
	"strconv"
	"strings"
	"time"

	"gorm.io/gorm"
)

type ServiceSql struct{}

func (s *ServiceSql) GetSqlList(RequestGetSqlList model.RequestGetSqlList) (sql interface{}, total int64, err error) {
	if RequestGetSqlList.SortBy == "" {
		RequestGetSqlList.SortBy = "id"
		RequestGetSqlList.Desc = false
	}
	pageSize := RequestGetSqlList.PageSize
	offset := RequestGetSqlList.PageSize * (RequestGetSqlList.Page - 1)
	db := global.DcDb.Model(&model.SysSql{})
	var sqlList []model.SysSql
	//配置搜索
	if RequestGetSqlList.SqlType != "" {
		db = db.Where("sql_type like ?", "%"+RequestGetSqlList.SqlType+"%")
	}

	if RequestGetSqlList.SchemaName != "" {
		db = db.Where("schema_name like ?", "%"+RequestGetSqlList.SchemaName+"%")
	}

	if RequestGetSqlList.TableName != "" {
		db = db.Where("table_name like ?", "%"+RequestGetSqlList.TableName+"%")
	}

	if RequestGetSqlList.SqlString != "" {
		db = db.Where("sql_string like ?", "%"+RequestGetSqlList.SqlString+"%")
	}

	if RequestGetSqlList.SqlCode != "" {
		db = db.Where("sql_code like ?", "%"+RequestGetSqlList.SqlCode+"%")
	}

	if RequestGetSqlList.CreatedBy != "" {
		db = db.Where("created_by like ?", "%"+RequestGetSqlList.CreatedBy+"%")
	}

	if RequestGetSqlList.UpdatedBy != "" {
		db = db.Where("updated_by like ?", "%"+RequestGetSqlList.UpdatedBy+"%")
	}

	if RequestGetSqlList.Memo != "" {
		db = db.Where("memo like ?", "%"+RequestGetSqlList.Memo+"%")
	}

	err = db.Count(&total).Error
	if err != nil {
		return
	}
	if pageSize == 0 {
		err = db.Order(model.OrderByColumn(RequestGetSqlList.SortBy, RequestGetSqlList.Desc)).Find(&sqlList).Error
	} else {
		err = db.Limit(pageSize).Offset(offset).Order(model.OrderByColumn(RequestGetSqlList.SortBy, RequestGetSqlList.Desc)).Find(&sqlList).Error
	}
	return sqlList, total, err
}

func (s *ServiceSql) EditSql(toEditSql model.SysSql, empeeAcct string) (err error) {
	var sysSql model.SysSql
	if err = global.DcDb.Where("id = ?", toEditSql.Id).First(&sysSql).Error; err != nil {
		return err
	}
	if *sysSql.Source == "internal" {
		return errors.New("系统内置不允许编辑：" + toEditSql.SqlCode)
	}

	if toEditSql.SqlType == "query" {
		rawSql, relatedSqlparams, err := s.ValidateSql(toEditSql.SqlString, empeeAcct)
		if err != nil {
			return err
		}
		toDropIfExistsTableStr := "DROP TABLE IF EXISTS " + global.DcConfig.Postgres.Schema + ".dc_sqltmp_" + toEditSql.SqlCode
		err = global.DcDb.Exec(toDropIfExistsTableStr).Error
		if err != nil {
			return err
		}
		toCreateTmpTableStr := "create table " + global.DcConfig.Postgres.Schema + ".dc_sqltmp_" + toEditSql.SqlCode + " as select t.* from ( " + rawSql + " ) t limit 0"
		err = global.DcDb.Exec(toCreateTmpTableStr).Error
		if err != nil {
			return err
		}
		err = global.DcDb.Save(&toEditSql).Error
		if err != nil {
			return err
		}
		err = global.DcDb.Model(&toEditSql).Association("Sqlparam").Replace(relatedSqlparams)
		if err != nil {
			return err
		}
	} else {
		err := s.ValidatePreDefinedTable(toEditSql.SqlType, toEditSql.HasDayId, toEditSql.SchemaName, toEditSql.TableName)
		if err != nil {
			return err
		}
		err = global.DcDb.Save(&toEditSql).Error
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *ServiceSql) AddSql(toAddSql model.SysSql, empeeAcct string) (err error) {
	var sql model.SysSql
	if !errors.Is(global.DcDb.Where("sql_code = ?", toAddSql.SqlCode).First(&sql).Error, gorm.ErrRecordNotFound) {
		return errors.New("此SQL编码已存在: " + toAddSql.SqlCode)
	}
	if toAddSql.SqlType == "query" {
		rawSql, relatedSqlparams, err := s.ValidateSql(toAddSql.SqlString, empeeAcct)
		if err != nil {
			return err
		}
		toDropIfExistsTableStr := "DROP TABLE IF EXISTS " + global.DcConfig.Postgres.Schema + ".dc_sqltmp_" + toAddSql.SqlCode
		err = global.DcDb.Exec(toDropIfExistsTableStr).Error
		if err != nil {
			return err
		}
		toCreateTmpTableStr := "create table " + global.DcConfig.Postgres.Schema + ".dc_sqltmp_" + toAddSql.SqlCode + " as select t.* from ( " + rawSql + " ) t limit 0"
		err = global.DcDb.Exec(toCreateTmpTableStr).Error
		if err != nil {
			return err
		}
		err = global.DcDb.Create(&toAddSql).Error
		if err != nil {
			return err
		}
		err = global.DcDb.Model(&toAddSql).Association("Sqlparam").Replace(relatedSqlparams)
		if err != nil {
			return err
		}
	} else {
		err := s.ValidatePreDefinedTable(toAddSql.SqlType, toAddSql.HasDayId, toAddSql.SchemaName, toAddSql.TableName)
		if err != nil {
			return err
		}
		err = global.DcDb.Create(&toAddSql).Error
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *ServiceSql) ValidatePreDefinedTable(sqlType string, hasDayId string, schemaName string, tableName string) (err error) {
	var table string
	err = global.DcDb.Raw("select tablename from pg_tables where schemaname = ? and tablename = ?", schemaName, tableName).Find(&table).Error
	if err != nil {
		return err
	}
	if table == "" {
		err = global.DcDb.Raw("select viewname from pg_views where schemaname = ? and viewname = ?", schemaName, tableName).Find(&table).Error
		if err != nil {
			return err
		}
	}
	var columns []model.SqlColumn
	err = global.DcDb.Raw("select column_name,data_type from information_schema.columns where table_schema = ? and table_name = ?", schemaName, tableName).Find(&columns).Error
	if err != nil {
		return err
	}
	// fmt.Println("---------------------------")
	// fmt.Printf("%+v\n", columns)
	day_id_flag := false
	mkt_area_name_flag := false
	dept_name_flag := false
	duty_zone_name_flag := false
	region_name_flag := false
	dev_code_flag := false
	for _, column := range columns {
		if column.ColumnName == "day_id" {
			day_id_flag = true
		}
		if column.ColumnName == "三级单元" {
			mkt_area_name_flag = true
		}
		if column.ColumnName == "四级单元" {
			dept_name_flag = true
		}
		if column.ColumnName == "包区" {
			duty_zone_name_flag = true
		}
		if column.ColumnName == "网格" {
			region_name_flag = true
		}
		if column.ColumnName == "发展代码" {
			dev_code_flag = true
		}
	}

	if hasDayId == "yes" && !day_id_flag {
		return errors.New("预定义表" + schemaName + "." + tableName + "中不存在day_id字段")

	}
	if sqlType == "preDefine3" && !mkt_area_name_flag {
		return errors.New("预定义三级单元汇总表" + schemaName + "." + tableName + "中不存在三级单元字段")
	}
	if sqlType == "preDefine4" && !mkt_area_name_flag {
		return errors.New("预定义四级单元汇总表" + schemaName + "." + tableName + "中不存在三级单元字段")
	}
	if sqlType == "preDefine4" && !dept_name_flag {
		return errors.New("预定义四级单元汇总表" + schemaName + "." + tableName + "中不存在四级单元字段")
	}
	if sqlType == "preDefine5" && !mkt_area_name_flag {
		return errors.New("预定义包区汇总表" + schemaName + "." + tableName + "中不存在三级单元字段")
	}
	if sqlType == "preDefine5" && !dept_name_flag {
		return errors.New("预定义包区汇总表" + schemaName + "." + tableName + "中不存在四级单元字段")
	}
	if sqlType == "preDefine5" && !duty_zone_name_flag {
		return errors.New("预定义包区汇总表" + schemaName + "." + tableName + "中不存在包区字段")
	}
	if sqlType == "preDefine6" && !mkt_area_name_flag {
		return errors.New("预定义网格汇总表" + schemaName + "." + tableName + "中不存在三级单元字段")
	}
	if sqlType == "preDefine6" && !dept_name_flag {
		return errors.New("预定义网格汇总表" + schemaName + "." + tableName + "中不存在四级单元字段")
	}
	if sqlType == "preDefine6" && !duty_zone_name_flag {
		return errors.New("预定义网格汇总表" + schemaName + "." + tableName + "中不存在包区字段")
	}
	if sqlType == "preDefine6" && !region_name_flag {
		return errors.New("预定义网格汇总表" + schemaName + "." + tableName + "中不存在网格字段")
	}
	if sqlType == "preDefineDev" && !mkt_area_name_flag {
		return errors.New("预定义发展人汇总表" + schemaName + "." + tableName + "中不存在三级单元字段")
	}
	if sqlType == "preDefineDev" && !dept_name_flag {
		return errors.New("预定义发展人汇总表" + schemaName + "." + tableName + "中不存在四级单元字段")
	}
	if sqlType == "preDefineDev" && !duty_zone_name_flag {
		return errors.New("预定义发展人汇总表" + schemaName + "." + tableName + "中不存在包区字段")
	}
	if sqlType == "preDefineDev" && !region_name_flag {
		return errors.New("预定义发展人汇总表" + schemaName + "." + tableName + "中不存在网格字段")
	}
	if sqlType == "preDefineDev" && !dev_code_flag {
		return errors.New("预定义发展人汇总表" + schemaName + "." + tableName + "中不存在发展代码字段")
	}
	if sqlType == "preDefineList" && !mkt_area_name_flag {
		return errors.New("预定义清单表" + schemaName + "." + tableName + "中不存在三级单元字段")
	}
	if sqlType == "preDefineList" && !dept_name_flag {
		return errors.New("预定义清单表" + schemaName + "." + tableName + "中不存在四级单元字段")
	}
	if sqlType == "preDefineList" && !duty_zone_name_flag {
		return errors.New("预定义清单表" + schemaName + "." + tableName + "中不存在包区字段")
	}
	if sqlType == "preDefineList" && !region_name_flag {
		return errors.New("预定义清单表" + schemaName + "." + tableName + "中不存在网格字段")
	}
	return nil
}

func (s *ServiceSql) ValidateSql(toTestSql string, empeeAcct string) (rawTranslatedSql string, associatedSqlparams *[]model.SysSqlparam, err error) {
	sqlStatement := toTestSql

	fmt.Printf("toTestSql: %v\n", sqlStatement)

	// 提取Sql语句中包含的Sqlparam参数
	sqlparamsInSql := global.DcSqlParamReg.FindAllString(sqlStatement, -1)
	var sqlparams []model.SysSqlparam
	for _, sqlparam := range sqlparamsInSql {
		if sqlparam == "@login_user_empee_acct" {
			sqlStatement = strings.Replace(sqlStatement, sqlparam, empeeAcct, -1)
			fmt.Println(sqlStatement)
			continue
		}
		if sqlparam[0] != '@' {
			err = errors.New("提取Sql语句中包含的Sqlparam参数错误! 提取到一个名为" + sqlparam + "的参数，但是参数名必须以@开头!")
			return "", nil, err
		}
		sqlparamWithOutAt := sqlparam[1:]
		// 匹配 sqlparam 表的数据
		var sqlparamInfo model.SysSqlparam
		err = global.DcDb.Where("sqlparam_code = ?", sqlparamWithOutAt).First(&sqlparamInfo).Error
		if err != nil {
			if err == gorm.ErrRecordNotFound {
				return "", nil, errors.New("未能匹配到名为" + sqlparamWithOutAt + "的参数，请检查参数是否已经定义或参数名是否正确!")
			} else {
				return "", nil, err
			}
		}
		// 替换 Sql 语句中的 Sqlparam 参数
		reg, err2 := regexp.Compile(sqlparam + `\b`)
		if err2 != nil {
			return "", nil, err2
		}
		if sqlparamInfo.SqlparamType == "date" && sqlparamInfo.UseVariable == "yes" {
			var res string
			sqlString := "select (" + sqlparamInfo.DefaultValue + ")::text"
			err = global.DcDb.Raw(sqlString).Find(&res).Error
			if err != nil {
				return "", nil, err
			}
			sqlStatement = reg.ReplaceAllLiteralString(sqlStatement, res)
		} else {
			sqlStatement = reg.ReplaceAllLiteralString(sqlStatement, sqlparamInfo.DefaultValue)
		}
		//sqlStatement = strings.Replace(sqlStatement, sqlparam, sqlparamInfo.DefaultValue, -1)
		sqlparams = append(sqlparams, sqlparamInfo)
	}

	result := map[string]interface{}{}
	stmt := global.DcDb.Session(&gorm.Session{DryRun: true}).Raw(sqlStatement).Statement
	fmt.Printf("stmt: %v\n", stmt.SQL.String())
	err = global.DcDb.Raw(sqlStatement).Scan(&result).Error
	if err != nil {
		return "", nil, err
	}
	fmt.Printf("result: %v\n", sqlparams)
	return stmt.SQL.String(), &sqlparams, err
}

func (s *ServiceSql) QuerySqlById(id uint) (SqlInfo model.SysSql, err error) {
	var sql model.SysSql
	err = global.DcDb.First(&sql, "id = ?", id).Error
	return sql, err
}

func (s *ServiceSql) QuerySqlBySqlCode(sql_code string) (SqlInfo *model.SysSql, err error) {
	fmt.Printf("sql_code: %s\n", sql_code)
	var sql model.SysSql
	err = global.DcDb.Preload("Sqlparam").First(&sql, "sql_code = ?", sql_code).Error
	fmt.Printf("sql: %v\n", sql)
	if sql.Sqlparam != nil {
		for i := range sql.Sqlparam {
			if sql.Sqlparam[i].SqlparamType == "date" && sql.Sqlparam[i].UseVariable == "yes" {
				var res string
				sqlString := "select (" + sql.Sqlparam[i].DefaultValue + ")::text"
				err = global.DcDb.Raw(sqlString).Find(&res).Error
				sql.Sqlparam[i].DefaultValue = res
			}
		}
	}
	return &sql, err
}

func (s *ServiceSql) DeleteSqlById(id uint) (err error) {
	var sysSql model.SysSql
	if err = global.DcDb.Where("id = ?", id).First(&sysSql).Error; err != nil {
		return err
	}
	if *sysSql.Source == "internal" {
		return errors.New("系统内置不允许删除：" + sysSql.SqlCode)
	}
	sqlCode := sysSql.SqlCode
	// 如果关联了菜单，则不允许删除
	var sysMenu model.SysMenu
	if err = global.DcDb.Where("sql_code = ?", sqlCode).First(&sysMenu).Error; err == nil {
		return errors.New("该Sql语句已经关联了菜单：" + sysMenu.Title + "，不允许删除")
	}
	if sysSql.SqlType == "query" {
		toDropIfExistsTableStr := "DROP TABLE IF EXISTS " + global.DcConfig.Postgres.Schema + ".dc_sqltmp_" + sysSql.SqlCode
		err = global.DcDb.Exec(toDropIfExistsTableStr).Error
		if err != nil {
			return err
		}
	}
	// 删除 sys_sql_sqlparam 表的关联
	err = global.DcDb.Where("sql_code = ?", sqlCode).Unscoped().Delete(&model.SysSqlSqlparam{}).Error
	//err = global.DcDb.Model(&sysSql).Association("Sqlparam").Clear()
	if err != nil {
		return err
	}
	// 删除 sys_sql 表的数据
	err = global.DcDb.Unscoped().Delete(&sysSql).Error
	if err != nil {
		return err
	}
	return err
}

func (s *ServiceSql) EditSqlSqlparam(toEditSqlSqlparam *model.RequestSqlSqlparamEdit) (err error) {
	err = global.DcDb.Where("sql_code=?", toEditSqlSqlparam.SqlCode).Delete(&model.SysSqlSqlparam{}).Error
	if err != nil {
		return err
	}
	if len(toEditSqlSqlparam.SqlSqlparam) != 0 {
		err = global.DcDb.Model(&model.SysSqlSqlparam{}).Create(&toEditSqlSqlparam.SqlSqlparam).Error
		return err
	}
	return nil
}

func (s *ServiceSql) QuerySqlResultBySqlCode(toQuerySqlCode model.RequestQueryBySqlCodeAndParams, empeeAcct string) (sqlResult []map[string]interface{}, total int64, err error) {
	var sysSql model.SysSql
	err = global.DcDb.First(&sysSql, "sql_code = ?", toQuerySqlCode.SqlCode).Error
	if err != nil {
		return nil, 0, err
	}
	var oriSql string
	if sysSql.SqlType == "query" {
		oriSql = sysSql.SqlString
		if res := strings.Contains(oriSql, "@login_user_empee_acct"); res {
			oriSql = strings.Replace(oriSql, "@login_user_empee_acct", empeeAcct, -1)
			fmt.Println(oriSql)
		}
		if toQuerySqlCode.Params != nil {
			for _, param := range toQuerySqlCode.Params {
				// if param.Value == "" {
				// 	return nil, 0, errors.New("参数值不能为空")
				// }
				if param.SqlparamCode == "" {
					return nil, 0, errors.New("参数名不能为空")
				}
				err = global.DcDb.Where("sqlparam_code = ?", param.SqlparamCode).First(&model.SysSqlparam{}).Error
				if err != nil {
					return nil, 0, errors.New("名为" + param.SqlparamCode + "的参数不存在, 请检查参数名是否正确: " + err.Error())
				}
				err = global.DcDb.Where("sqlparam_code = ? and sql_code = ?", param.SqlparamCode, toQuerySqlCode.SqlCode).First(&model.SysSqlSqlparam{}).Error
				if err != nil {
					return nil, 0, errors.New("名为" + param.SqlparamCode + "的参数与SQL未定义关联关系, 请检查参数名是否正确: " + err.Error())
				}
				// 替换 Sql 语句中的 Sqlparam 参数
				reg, err2 := regexp.Compile(`@` + param.SqlparamCode + `\b`)
				if err2 != nil {
					return nil, 0, errors.New("编译正则替换" + param.SqlparamCode + "时出错，原始报错信息:" + err2.Error())
				}
				oriSql = reg.ReplaceAllLiteralString(oriSql, param.Value)
			}
		}
	} else {
		oriSql = "select * from " + sysSql.SchemaName + "." + sysSql.TableName
		//TODO: 未完成,这里需要加校验，现在只是简单的添加了所有传来的参数
		if toQuerySqlCode.Params != nil {
			oriSql += " where "
		}
		for _, param := range toQuerySqlCode.Params {
			if param.Value == "" {
				continue
			}
			if param.SqlparamCode == "" {
				return nil, 0, errors.New("参数名不能为空")
			}
			if param.SqlparamCode == "begin_date" {
				oriSql += "day_id >= '" + param.Value + "' and "
			} else if param.SqlparamCode == "end_date" {
				oriSql += "day_id <= '" + param.Value + "' and "
			} else {
				oriSql += param.SqlparamName + " = '" + param.Value + "' and "
			}
		}
		oriSql = strings.TrimSuffix(oriSql, " and ")

	}

	fmt.Println("oriSql:", oriSql)
	global.DcDb.Raw(oriSql).Scan(&sqlResult)
	if err != nil {
		return
	}
	//fmt.Printf("%+v\n", sqlResult)
	return sqlResult, int64(len(sqlResult)), nil
}

func (s *ServiceSql) QuerySqlColumnsBySqlCode(sqlCode string) (sqlColumns []model.SqlColumn, total int64, err error) {
	var sysSql model.SysSql
	var jsonagg model.ResponseJsonAgg
	err = global.DcDb.First(&sysSql, "sql_code = ?", sqlCode).Error
	if err != nil {
		return nil, 0, err
	}
	if sysSql.SqlType == "query" {

		err = global.DcDb.Raw("select json_agg(t) jsonagg from (SELECT column_name, data_type FROM information_schema.columns WHERE  table_name = ? and table_schema=? ORDER  BY ordinal_position) t", "dc_sqltmp_"+sqlCode, global.DcConfig.Postgres.Schema).First(&jsonagg).Error
		if err != nil {
			return nil, 0, err
		}
	} else {
		err = global.DcDb.Raw("select json_agg(t) jsonagg from (SELECT column_name, data_type FROM information_schema.columns WHERE  table_name = ? and table_schema=? ORDER  BY ordinal_position) t", sysSql.TableName, sysSql.SchemaName).First(&jsonagg).Error
		if err != nil {
			return nil, 0, err
		}
	}
	//fmt.Printf("%+v\n", jsonagg.Jsonagg)

	var colMap []map[string]interface{}
	var colArr []model.SqlColumn
	err = json.Unmarshal([]byte(jsonagg.Jsonagg), &colMap)
	if err != nil {
		return nil, 0, err
	}
	for _, col := range colMap {
		var colObj model.SqlColumn
		colObj.ColumnName = fmt.Sprintf("%s", col["column_name"])
		dataType := fmt.Sprintf("%s", col["data_type"])
		switch dataType {
		case "character varying", "character", "char", "varchar", "text":
			colObj.DataType = "text"
		case "integer", "bigint", "smallint", "decimal", "numeric", "double precision", "real", "float", "smallserial", "serial", "bigserial":
			colObj.DataType = "int"
		case "timestamp with time zone", "timestamp without time zone", "date", "time", "time with time zone", "time without time zone":
			colObj.DataType = "datetime"
		case "boolean":
			colObj.DataType = "bool"
		default:
			colObj.DataType = "unknown"
		}
		colArr = append(colArr, colObj)
	}
	return colArr, int64(len(colArr)), nil
}

func (s *ServiceSql) QueryDirectSql(sqlStr string) (sqlCols map[string]int, sqlResult []map[string]interface{}, total int64, err error) {
	timestampStr := strconv.FormatInt(time.Now().UnixNano(), 10)
	toCreateTmpTableStr := "create table " + global.DcConfig.Postgres.Schema + ".dc_exceltmp_" + timestampStr + " as select t.* from ( " + sqlStr + " ) t limit 0"
	err = global.DcDb.Exec(toCreateTmpTableStr).Error
	if err != nil {
		return nil, nil, 0, err
	}
	var jsonagg model.ResponseJsonAgg
	err = global.DcDb.Raw("select json_agg(t) jsonagg from (SELECT column_name FROM information_schema.columns WHERE  table_name = ? and table_schema=? ORDER  BY ordinal_position) t", "dc_exceltmp_"+timestampStr, global.DcConfig.Postgres.Schema).First(&jsonagg).Error
	if err != nil {
		return nil, nil, 0, err
	}
	var colMap []map[string]interface{}
	err = json.Unmarshal([]byte(jsonagg.Jsonagg), &colMap)
	if err != nil {
		return nil, nil, 0, err
	}
	colOrder := make(map[string]int)
	k := 1
	for _, col := range colMap {
		colOrder[fmt.Sprintf("%s", col["column_name"])] = k
		k++
	}
	fmt.Printf("%+v\n", colOrder)

	err = global.DcDb.Raw(sqlStr).Scan(&sqlResult).Error
	if err != nil {
		return
	}
	defer global.DcDb.Exec("drop table " + global.DcConfig.Postgres.Schema + ".dc_exceltmp_" + timestampStr)
	// fmt.Printf("%+v\n", sqlResult)
	return colOrder, sqlResult, int64(len(sqlResult)), nil
}
